package cn.edu.njust.dao;

import cn.edu.njust.entity.Course;
import cn.edu.njust.util.JDBCUtils;

import java.sql.*;
import java.util.ArrayList;

public class CourseDAOImpl implements CourseDAO{
    @Override
    public ArrayList<Course> selectByPage(int type, int start) {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        ArrayList<Course> list = new ArrayList<>();
        try {
            conn = JDBCUtils.getConnection();
            String sql = "select * from course";        //SQL语句
            if (type == 1) {
                sql += " order by cName";
            }else if(type == 2){
                sql += " order by cName desc";
            }else if(type == 3){
                sql += " order by cNum";
            }else if(type == 4){
                sql += " order by cNum desc";
            }
            sql += " limit ?,10";
            pstmt = conn.prepareStatement(sql);
            pstmt.setInt(1,start);
            rs = pstmt.executeQuery();
            while(rs.next()){
                Course course= new Course();
                course.setId(rs.getInt("cId"));
//                System.out.println(rs.getInt("cId"));
                course.setName(rs.getString("cName"));
//                System.out.println("haha"+rs.getString("cName"));
                course.setNum(rs.getInt("cNum"));
                course.setType(rs.getInt("cType"));
                list.add(course);
            }
            return list;
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            JDBCUtils.close(rs,pstmt,conn);
        }
        return null;
    }

    public ArrayList<Course> selectByPage(int start){
        return selectByPage(0,start);
    }

    @Override
    public ArrayList<Course> selectAll(int i) {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        ArrayList<Course> list = new ArrayList<>();
        try {
            conn = JDBCUtils.getConnection();
            String sql = "select * from course";        //SQL语句
            if(i == 1)//课程名称升序代号
                sql += " order by cName";
            else if(i == 2)
                sql += " order by cName desc";
            else if(i == 3)
                sql += " order by cNum";
            else if( i == 4)
                sql += " order by cNum desc";
            pstmt = conn.prepareStatement(sql);
            rs = pstmt.executeQuery();
            while(rs.next()){
                Course course= new Course();
                course.setId(rs.getInt("cId"));
                course.setName(rs.getString("cName"));
                course.setNum(rs.getInt("cNum"));
                course.setType(rs.getInt("cType"));
                list.add(course);
            }
            return list;
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            JDBCUtils.close(rs,pstmt,conn);
        }
        return null;
    }

    @Override
    public ArrayList<Course> selectAll() {
        return selectAll(0);
    }

    @Override
    public int add(Course course) {
        Connection conn = null;
        PreparedStatement pstmt = null;
        int rtn = 0;
        int flag = 0;
        Course c = FindByName(course.getName());
        if(c != null){
            return 2;       //返回值为2：说明该课程已存在，不需要添加
        }
        try {               //c为空，正常添加
            conn = JDBCUtils.getConnection();
            String sql = "insert into course values(null,?,?,?)";        //SQL语句
            pstmt = conn.prepareStatement(sql);
            pstmt.setString(1, course.getName());
            pstmt.setInt(2,course.getNum());
            pstmt.setInt(3,course.getType());
            flag =  pstmt.executeUpdate();
            if(flag != 0){
                rtn = 1;        //更新顺利执行，返回1
            }

        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            JDBCUtils.close(pstmt,conn);
        }
        return rtn;
    }

    @Override
    public boolean sub(int id) {
            Connection conn = null;
            PreparedStatement pstmt = null;
            boolean isSuc = false;
            int flag = 0;
            try {
                conn = JDBCUtils.getConnection();
                String sql = "delete from course where cId=?";        //SQL语句
                pstmt = conn.prepareStatement(sql);
                pstmt.setInt(1,id);
                flag =  pstmt.executeUpdate();
                if(flag != 0){
                    isSuc = true;
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }finally {
                JDBCUtils.close(pstmt,conn);
            }
            return isSuc;
    }

    @Override
    public Course FindByName(String name) {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        try {
            conn = JDBCUtils.getConnection();
            String sql = "select * from course where cName=?";        //SQL语句
            pstmt = conn.prepareStatement(sql);
            pstmt.setString(1,name);
            rs = pstmt.executeQuery();
            if(rs.next()){
                Course course = new Course();
                course.setId(rs.getInt("cId"));
                course.setName(name);
                course.setType(rs.getInt("cType"));
                return course;
            }
            return null;
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            JDBCUtils.close(rs,pstmt,conn);
        }
        return null;
    }

    @Override
    public Course FindById(int id) {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        try {
            conn = JDBCUtils.getConnection();
            String sql = "select * from course where cId=?";        //SQL语句
            pstmt = conn.prepareStatement(sql);
            pstmt.setInt(1,id);
            rs = pstmt.executeQuery();
            if(rs.next()){
                Course course = new Course();
                course.setId(rs.getInt("cId"));
                course.setName(rs.getString("cName"));
                course.setType(rs.getInt("cType"));
                return course;
            }
            return null;
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            JDBCUtils.close(rs,pstmt,conn);
        }
        return null;
    }

    @Override
    public int getTotalCount() {        //获取数据库中的课程数目
        // sql：SELECT COUNT(cId) FROM course
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        int RowCount = 0;
        ArrayList<Course> list = new ArrayList<Course>();
        try {
            conn = JDBCUtils.getConnection();
            String sql = "select count(cId) from course";        //SQL语句
            pstmt = conn.prepareStatement(sql);
            rs = pstmt.executeQuery();
            rs.next();
            RowCount = rs.getInt(1);
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            JDBCUtils.close(rs,pstmt,conn);
        }
        return RowCount;
    }


    public static void main(String[] args) {
        CourseDAOImpl dao = new CourseDAOImpl();
        System.out.println(dao.getTotalCount());
        ArrayList<Course> list = dao.selectByPage(1);
        System.out.println(list);
    }
}
